﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using DevExpress.XtraEditors;

namespace QLKS10_11
{
    public class Dataserver : DataTable
    {
        #region thuộc tính
        public static String DatabaseName;
        public static SqlConnection m_Connection;
        private SqlDataAdapter m_DataAdapter;
        private SqlCommand m_Command;
        public static String m_ConnectionString = @"Data Source=PI-PC\SQLEXPRESS;Initial Catalog=QLKS10_11;Integrated Security=True";
        #endregion
        #region Phương Thức Dựng DataService
        public Dataserver()
        {
            OpenConnection();
        }
        #endregion
        #region Phương Thức Dựng Command
        public SqlCommand Command
        {
            get { return m_Command; }
            set { m_Command = value; }
        }
        #endregion
        #region Lấy dữ liệu
        public void Load(SqlCommand command)
        {
            m_Command = command;

            try
            {
                this.Clear();
                m_Command.Connection = m_Connection;
                //m_Command.CommandType = CommandType.StoredProcedure;
                m_DataAdapter = new SqlDataAdapter();
                m_DataAdapter.SelectCommand = m_Command;
                m_DataAdapter.Fill(this);


            }
            catch (Exception e)
            {
                XtraMessageBox.Show(e.Message);
            }
        }
        #endregion
        #region Mở kết nối
        public static bool OpenConnection()
        {
            try
            {
                if (m_Connection == null)
                    m_Connection = new SqlConnection(m_ConnectionString);
                if (m_Connection.State == ConnectionState.Closed)
                    m_Connection.Open();
                return true;
            }
            catch
            {
                m_Connection.Close();
                return false;
            }

        }
        #endregion
        #region đóng kết nối
        public void CloseConnection()
        {
            m_Connection.Close();
        }
        #endregion
        #region Update dữ liệu
        public void Update()
        {
            SqlCommandBuilder builder = new SqlCommandBuilder(m_DataAdapter);
            m_DataAdapter.Update(this);
        }
        #endregion
        #region thực thi câu lệnh không trả về kết quả không có select truyen vào
        public int ExecuteNoneQuery()
        {

            int result = 0;
            SqlTransaction tr = null;
            // m_Dataset = new DataSet(); 
            try
            {
                tr = m_Connection.BeginTransaction();

                m_Command.Connection = m_Connection;
                m_Command.Transaction = tr;
                m_DataAdapter = new SqlDataAdapter();
                m_DataAdapter.SelectCommand = m_Command;
                SqlCommandBuilder builder = new SqlCommandBuilder(m_DataAdapter);
                result = m_DataAdapter.Update(this);
                tr.Commit();

            }
            catch (Exception e)
            {
                if (tr != null)
                    tr.Rollback();
                XtraMessageBox.Show(e.Message);
            }
            return result;
        }
        #endregion
        #region thực thi câu lệnh không trả về kết quả có select truyen vào
        public int ExecuteNoneQuery(SqlCommand cmd)
        {

            int result = 0;
            SqlTransaction tr = null;

            try
            {
                tr = m_Connection.BeginTransaction();

                cmd.Connection = m_Connection;

                cmd.Transaction = tr;

                result = cmd.ExecuteNonQuery();

                this.AcceptChanges();

                tr.Commit();

            }
            catch (Exception e)
            {
                if (tr != null) tr.Rollback();
                throw e;
            }
            return result;

        }
        #endregion
        #region trả về giá trị
        public object ExecuteScalar(SqlCommand cmd)
        {
            object result = null;
            SqlTransaction tr = null;

            try
            {
                tr = m_Connection.BeginTransaction();

                cmd.Connection = m_Connection;

                cmd.Transaction = tr;

                result = cmd.ExecuteScalar();

                this.AcceptChanges();

                tr.Commit();

                if (result == DBNull.Value)
                {
                    result = null;
                }
            }
            catch
            {
                if (tr != null) tr.Rollback();
                throw;
            }
            return result;
        }
        #endregion
        #region Hủy kết nối
        public void DisposeConnect()
        {
            m_Connection.Dispose();
        }
        #endregion
        #region Hủy vận chuyển
        public void DisposeDataAdapter()
        {
            if (m_DataAdapter != null)
                m_DataAdapter.Dispose();
        }
        #endregion
    }
}
